﻿using Microsoft.Extensions.DependencyInjection;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using SqlSugar;
using SqlSugar.Extensions;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
//using Repository;
using Microsoft.Extensions.Configuration;

namespace WebAPI
{
    /// <summary>
    /// SqlSugar 启动服务
    /// </summary>
    public static class SqlsugarSetup
    {
        public static void AddSqlsugarSetup(this IServiceCollection services)
        {
            var DBConfig = MutiInitConn();
            //后台固定所有库
            //var configConnection = new List<ConnectionConfig>
            //{
            //    new ConnectionConfig(){ConfigId="A",DbType=DbType.SqlServer,ConnectionString="",IsAutoCloseConnection=true},
            //    new ConnectionConfig(){ConfigId="A",DbType=DbType.SqlServer,ConnectionString="",IsAutoCloseConnection=true}
            //};

            //appsettings.js 动态获取
            var configConnection = new List<ConnectionConfig>();
            DBConfig.DBS.ForEach(m =>
            {
                configConnection.Add(new ConnectionConfig()
                {
                    ConfigId = m.ConnId/*.ToLower()*/,
                    ConnectionString = m.Connection,
                    DbType = (DbType)m.DBType,
                    IsAutoCloseConnection = true,
                    MoreSettings = new ConnMoreSettings()
                    {
                        IsAutoRemoveDataCache = true
                    }
                    //InitKeyType = InitKeyType.SystemTable
                });
            });

            //多租户循环添加AOP
            SqlSugarClient sqlSugar = new SqlSugarClient(configConnection);
            configConnection.ForEach(c =>
            {
                SqlSugarProvider client = sqlSugar.GetConnection(c.ConfigId);
                //每次Sql执行前事件
                client.Aop.OnLogExecuting = (sql, pars) =>
                {
                    var queryString = new KeyValuePair<string, SugarParameter[]>(sql, pars);
                    if (sql.StartsWith("UPDATE") || sql.StartsWith("INSERT"))
                    {
                        Console.ForegroundColor = ConsoleColor.Blue;
                        Console.WriteLine($"==============新增/修改操作==============");
                        Console.WriteLine(ToSqlExplain.GetSql(queryString));
                    }
                    if (sql.StartsWith("DELETE"))
                    {
                        Console.ForegroundColor = ConsoleColor.Red;
                        Console.WriteLine($"==============删除操作==============");
                    }
                    if (sql.StartsWith("SELECT"))
                    {
                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.WriteLine($"==============查询操作==============");
                    }
                    Console.WriteLine(ToSqlExplain.GetSql(queryString));
                    Console.ForegroundColor = ConsoleColor.White;
                };
                //每次Sql执行后事件
                client.Aop.OnLogExecuted = (sql, pars) =>
                {
                    //执行时间超过10秒
                    if (client.Ado.SqlExecutionTime.TotalSeconds > 10)
                    {
                        Console.WriteLine(sql);
                    }
                };
                //SQL报错
                client.Aop.OnError = (exp) =>
                {
                    Console.WriteLine(exp.Sql);
                };
            });
            //SqlSugarScope(单列模式 ) 用AddSingleton 
            //多个连接对象注入服务，如果有事务操作必须采用 AddScoped
            services.AddSingleton<ISqlSugarClient>(sqlSugar);
        }

        //public static DbConfig MutiConnectionString => MutiInitConn();

        /// <summary>
        /// 解析appsettings
        /// </summary>
        /// <returns></returns>
        public static DbConfig MutiInitConn()
        {
            var dBConnection = new DbConfig();
            var listdatabase = new List<DBS>();
            string Path = "appsettings.json";
            using (var file = new StreamReader(Path))
            using (var reader = new JsonTextReader(file))
            {
                var jObj = (JObject)JToken.ReadFrom(reader);
                dBConnection.MainDB = jObj["MainDB"].ObjToString();
                dBConnection.MutiDBEnabled = jObj["MutiDBEnabled"].ObjToBool();
                if (!string.IsNullOrWhiteSpace("DBS"))
                {
                    var secJt = jObj["DBS"];
                    if (secJt != null)
                    {
                        for (int i = 0; i < secJt.Count(); i++)
                        {
                            if (secJt[i]["Enabled"].ObjToBool())
                            {
                                listdatabase.Add(new DBS()
                                {
                                    ConnId = secJt[i]["ConnId"].ObjToString(),
                                    Connection = secJt[i]["Connection"].ObjToString(),
                                    DBType = (secJt[i]["DBType"].ObjToInt()),
                                });
                            }
                        }
                    }
                }

                dBConnection.DBS = listdatabase;
                return dBConnection;
            }
        }

        /// <summary>
        /// 拼接出完整的Sql，方便查看
        /// </summary>
        public class ToSqlExplain
        {
            public static string GetSql(KeyValuePair<string, SugarParameter[]> queryString)
            {
                var sql = queryString.Key;//sql语句
                var par = queryString.Value;//参数

                //字符串替换MethodConst1x会替换掉MethodConst1所有要从后往前替换,不能用foreach,后续可以优化
                for (int i = par.Length - 1; i >= 0; i--)
                {
                    if (par[i].ParameterName.StartsWith("@") && par[i].ParameterName.Contains("UnionAll"))
                    {
                        sql = sql.Replace(par[i].ParameterName, par[i].Value.ToString());
                    }
                }

                for (int i = par.Length - 1; i >= 0; i--)
                {
                    if (par[i].ParameterName.StartsWith("@Method"))
                    {
                        sql = sql.Replace(par[i].ParameterName, "'" + par[i].Value.ToString() + "'");
                    }
                }
                for (int i = par.Length - 1; i >= 0; i--)
                {
                    if (par[i].ParameterName.StartsWith("@Const"))
                    {
                        sql = sql.Replace(par[i].ParameterName, par[i].Value.ToString());
                    }
                }
                for (int i = par.Length - 1; i >= 0; i--)
                {
                    if (par[i].ParameterName.StartsWith("@"))
                    {
                        //值拼接单引号 拿出来的sql不会报错
                        sql = sql.Replace(par[i].ParameterName, "'" + Convert.ToString(par[i].Value) + "'");
                    }
                }
                return sql;
            }
        }

    }
}
